package com.bysj.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bysj.dao.UserDao;
import com.bysj.entity.User;
import com.bysj.util.Dbutils;

public class UserDaoImpl implements UserDao {
	private Dbutils db=new Dbutils();
	@Override
	public void addUser(String sql,User user) {
		Connection  conn=db.getConnection();
		PreparedStatement  pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, user.getName());
			pst.setString(2, user.getAddress());
			pst.setString(3, user.getEmail());
			pst.setString(4, user.getPasswd());
			pst.setString(5, user.getTel());
			pst.setString(6, user.getSex());
			pst.setString(7, user.getBirthday());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}
	@Override
	public void delUser(String sql,User user) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, user.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public void updateUser(String sql,User user) {
		Connection  conn=db.getConnection();
		PreparedStatement pst=null;

		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, user.getName());
			pst.setString(2, user.getAddress());
			pst.setString(3, user.getBirthday());
			pst.setString(4, user.getTel());
			pst.setString(5, user.getSex());
			pst.setString(6, user.getNation());
			pst.setString(7, user.getEducation());
			pst.setString(8, user.getDescribeself());
			pst.setString(9, user.getSchool());
			pst.setInt(10, user.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public User queryUser(String sql,User u) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		User user=new User();
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, u.getId());
			set=pst.executeQuery();
			if(set.next()){
				user.setId(set.getInt(1));
				user.setName(set.getString(2));
				user.setAddress(set.getString(3));
				user.setBirthday(set.getString(4));
				user.setEmail(set.getString(5));
				user.setTel(set.getString(6));
				user.setPic(set.getString(7));
				user.setSex(set.getString(8));
				user.setNation(set.getString(9));
				user.setEducation(set.getString(10));
				user.setSchool(set.getString(12));
				user.setDescribeself(set.getString(11));
				user.setPasswd(set.getString(13));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return user;
	}

	@Override
	public ArrayList<User> queryAllUser(String sql) {
		ArrayList<User> list=new ArrayList<User>();
		Connection conn=db.getConnection();
		PreparedStatement  pst=null;
		ResultSet set=null;
		User user=null;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			while(set.next()){
				user =new User();
				user.setId(set.getInt(1));
				user.setName(set.getString(2));
				user.setAddress(set.getString(3));
				user.setBirthday(set.getString(4));
				user.setEmail(set.getString(5));
				user.setTel(set.getString(6));
				user.setPic(set.getString(7));
				user.setSex(set.getString(8));
				user.setNation(set.getString(9));
				user.setEducation(set.getString(10));
				user.setSchool(set.getString(12));
				user.setDescribeself(set.getString(11));
				user.setPasswd(set.getString(13));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public int CountAllUser(String sql) {
		Connection  conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		int count=0;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery(sql);
			if(set.next()){
				count=set.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return count;
	}

	@Override
	public ArrayList<User> FenyyeQuery(String sql, int page, int rows) {
		ArrayList<User> list=new ArrayList<User>();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		User user=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			set=pst.executeQuery();
			while(set.next()){
				user=new User();
				user =new User();
				user.setId(set.getInt(1));
				user.setName(set.getString(2));
				user.setAddress(set.getString(3));
				user.setBirthday(set.getString(4));
				user.setEmail(set.getString(5));
				user.setTel(set.getString(6));
				user.setPic(set.getString(7));
				user.setSex(set.getString(8));
				user.setNation(set.getString(9));
				user.setEducation(set.getString(10));
				user.setSchool(set.getString(12));
				user.setDescribeself(set.getString(11));
				user.setPasswd(set.getString(13));
				list.add(user);		
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}
	@Override
	public boolean checkUserLogin(String sql, User u) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		boolean b=false;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, u.getEmail());
			pst.setString(2, u.getPasswd());
			if(pst.executeQuery().next()){
				b=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
		return b;
	}
	@Override
	public User queryUserByemailAndpasswd(String sql, User u) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		User user=new User();
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, u.getEmail());
			pst.setString(2, u.getPasswd());
			set=pst.executeQuery();
			if(set.next()){
				user.setId(set.getInt(1));
				user.setName(set.getString(2));
				user.setAddress(set.getString(3));
				user.setBirthday(set.getString(4));
				user.setEmail(set.getString(5));
				user.setTel(set.getString(6));
				user.setPic(set.getString(7));
				user.setSex(set.getString(8));
				user.setNation(set.getString(9));
				user.setEducation(set.getString(10));
				user.setSchool(set.getString(12));
				user.setDescribeself(set.getString(11));
				user.setPasswd(set.getString(13));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return user;
	}
	@Override
	public boolean checkRegEmail(String sql, User user) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		boolean b=false;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			while(set.next()){
				if(user.getEmail().equals(set.getString(1))){
					b=false;
					break;
				}else{
					b=true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return b;
	}
	@Override
	public User queryUserByemail(String sql, User us) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		User user=new User();
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, us.getEmail());
			set=pst.executeQuery();
			if(set.next()){
				user.setId(set.getInt(1));
				user.setName(set.getString(2));
				user.setAddress(set.getString(3));
				user.setBirthday(set.getString(4));
				user.setEmail(set.getString(5));
				user.setTel(set.getString(6));
				user.setPic(set.getString(7));
				user.setSex(set.getString(8));
				user.setNation(set.getString(9));
				user.setEducation(set.getString(10));
				user.setSchool(set.getString(12));
				user.setDescribeself(set.getString(11));
				user.setPasswd(set.getString(13));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return user;
	}
	@Override
	public void updateUserPic(String sql, User user) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, user.getPic());
			pst.setInt(2, user.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
		
	}

}
